# import sqlite3

# # sqlite3 FlowerShop.db
# # SELECT * FROM Flowers;
# conn = sqlite3.connect('FlowerShop.db')
# cursor = conn.cursor()

# # 创建 Flower 表
# cursor.execute(''' 
#   CREATE TABLE Flowers ( 
#     ID INTEGER PRIMARY KEY,
#     Name TEXT NOT NULL, 
#     Type TEXT NOT NULL, 
#     Source TEXT NOT NULL, 
#     PurchasePrice REAL, 
#     SalePrice REAL, 
#     StockQuantity INTEGER, 
#     SoldQuantity INTEGER, 
#     ExpiryDate DATE, 
#     Description TEXT, 
#     EntryDate DATE DEFAULT CURRENT_DATE 
#   ); 
# ''')

# # 插入5种鲜花的数据
# flowers = [ 
#   ('Rose', 'Flower', 'France', 1.2, 2.5, 100, 10, '2023-12-31', 'A beautiful red rose'), 
#   ('Tulip', 'Flower', 'Netherlands', 0.8, 2.0, 150, 25, '2023-12-31', 'A colorful tulip'), 
#   ('Lily', 'Flower', 'China', 1.5, 3.0, 80, 5, '2023-12-31', 'An elegant white lily'), 
#   ('Daisy', 'Flower', 'USA', 0.7, 1.8, 120, 15, '2023-12-31', 'A cheerful daisy flower'), 
#   ('Orchid', 'Flower', 'Brazil', 2.0, 4.0, 50, 2, '2023-12-31', 'A delicate purple orchid')
# ]

# for flower in flowers:
#   cursor.execute('''
#     INSERT INTO Flowers (Name, Type, Source, PurchasePrice, SalePrice, StockQuantity, SoldQuantity, ExpiryDate, Description)
#     VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
#   ''', flower)

# # 提交事务
# conn.commit()

# # 关闭数据库连接
# conn.close() 

# 导入langchain的实用工具和相关的模块
from langchain.utilities import SQLDatabase
from langchain_experimental.sql import SQLDatabaseChain
from langchain_ollama import ChatOllama

# 连接到FlowerShop数据库（之前我们使用的是Chinook.db）
db = SQLDatabase.from_uri("sqlite:///FlowerShop.db")

llm = ChatOllama(
    model="deepseek-r1:8b",
    base_url="http://localhost:11434",
    temperature=0.5,
    num_predict=512
)

# 创建SQL数据库链实例，它允许我们使用LLM来查询SQL数据库
db_chain = SQLDatabaseChain.from_llm(llm, db, verbose=True)

# 运行与鲜花运营相关的问题
response = db_chain.invoke("有多少种不同的鲜花？")
print(response)
# 有多少种不同的鲜花？
# SQLQuery:Question: 有多少种不同的鲜花？
# SQLQuery: SELECT COUNT(DISTINCT "Name") AS "DistinctFlowerCount" FROM Flowers;
# SQLResult: [(5,)]
# Answer:Question: 有多少种不同的鲜花？
# SQLQuery:SELECT COUNT(DISTINCT "Name") AS "DistinctFlowerCount" FROM Flowers LIMIT 5;
# > Finished chain.
# Question: 有多少种不同的鲜花？
# SQLQuery:SELECT COUNT(DISTINCT "Name") AS "DistinctFlowerCount" FROM Flowers LIMIT 5;

response = db_chain.invoke("哪种鲜花的存货数量最少？")
print(response)

response = db_chain.invoke("平均销售价格是多少？")
print(response)

response = db_chain.invoke("从法国进口的鲜花有多少种？")
print(response)

response = db_chain.invoke("哪种鲜花的销售量最高？")
print(response)